﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using PBOffice.Models;

namespace PBOffice.DAL
{
    public class MeetingRoomService
    {
        public List<MeetingRoom> GetAllMeetingRoom()
        {
            List<MeetingRoom> list = new List<MeetingRoom>();
            string sql = "select m.*,t.* from MeetingRoom m,TableType t where m.TypeId=t.TypeId";
            using (SqlDataReader dr = SqlHelp.ExecuteReader(SqlHelp.connectionstring, CommandType.Text, sql))
            {
                while (dr.Read())
                {
                    TableType type = new TableType();
                    type.TypeId = (int)dr["TypeId"];
                    type.TypeName = dr["TypeName"].ToString();
                    MeetingRoom room = new MeetingRoom();
                    room.Counts = (int)dr["Counts"];
                    room.Equipment = dr["Equipment"].ToString();
                    room.RoomArea = Convert.ToDouble(dr["RoomArea"]);
                    room.RoomImge = dr["RoomImge"].ToString();
                    room.RoomName = dr["RoomName"].ToString();
                    room.RoomNo = dr["RoomNo"].ToString();
                    room.RoomType = type;
                    list.Add(room);
                }
            }
            return list;
        }
        public MeetingRoom GetAllMeetingRoom(string no)
        {
            MeetingRoom room = new MeetingRoom();
            string sql = string.Format("select m.*,t.* from MeetingRoom m,TableType t where m.TypeId=t.TypeId and m.RoomNo='{0}'", no);
            using (SqlDataReader dr = SqlHelp.ExecuteReader(SqlHelp.connectionstring, CommandType.Text, sql))
            {
                while (dr.Read())
                {
                    TableType type = new TableType();
                    type.TypeId = (int)dr["TypeId"];
                    type.TypeName = dr["TypeName"].ToString();

                    room.Counts = (int)dr["Counts"];
                    room.Equipment = dr["Equipment"].ToString();
                    room.RoomArea = Convert.ToDouble(dr["RoomArea"]);
                    room.RoomImge = dr["RoomImge"].ToString();
                    room.RoomName = dr["RoomName"].ToString();
                    room.RoomNo = dr["RoomNo"].ToString();
                    room.RoomType = type;

                }
            }
            return room;
        }
        public void AddMeetingRoom(MeetingRoom room)
        {
            string sql = "insert [MeetingRoom] (RoomNo,RoomName,RoomArea,Counts,TypeId,Equipment,RoomImge) "
                + "values(@RoomNo,@RoomName,@RoomArea,@Counts,@TypeId,@Equipment,@RoomImge)";
            SqlParameter[] spa = {
                                     new SqlParameter("@RoomNo",room.RoomNo),
                                      new SqlParameter("@RoomName",room.RoomName),
                                       new SqlParameter("@RoomArea",room.RoomArea),
                                       new SqlParameter("@Counts",room.Counts),
                                       new SqlParameter("@TypeId",room.RoomType.TypeId),
                                       new SqlParameter("@Equipment",room.Equipment),
                                       new SqlParameter("@RoomImge",room.RoomImge)
                                 };
            SqlHelp.ExecuteNonQuery(SqlHelp.connectionstring, CommandType.Text, sql, spa);
        }
        public void UpdateMeetingRoom(MeetingRoom room)
        {
            string sql = "update [MeetingRoom] set RoomName=@RoomName,RoomArea=@RoomArea,Counts=@Counts,TypeId=@TypeId,"
            + "Equipment=@Equipment,RoomImge=@RoomImge where RoomNo=@RoomNo";
            SqlParameter[] spa = {
                                     new SqlParameter("@RoomNo",room.RoomNo),
                                      new SqlParameter("@RoomName",room.RoomName),
                                       new SqlParameter("@RoomArea",room.RoomArea),
                                       new SqlParameter("@Counts",room.Counts),
                                       new SqlParameter("@TypeId",room.RoomType.TypeId),
                                       new SqlParameter("@Equipment",room.Equipment),
                                       new SqlParameter("@RoomImge",room.RoomImge)
                                 };
            SqlHelp.ExecuteNonQuery(SqlHelp.connectionstring, CommandType.Text, sql, spa);
        }
        public void DeleteMeetingRoom(string no)
        {
            string sql = string.Format("delete [MeetingRoom] where RoomNo='{0}'", no);
            SqlHelp.ExecuteNonQuery(SqlHelp.connectionstring, CommandType.Text, sql);
        }
    }
}
